REM  SQL scripts for Chapter 5 of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.


create role APPLICATION_USER;
grant CREATE SESSION to APPLICATION_USER;
grant APPLICATION_USER to username;

select AVG(NVL(VSIZE(Column1),0))+
       AVG(NVL(VSIZE(Column2),0))+
       AVG(NVL(VSIZE(Column3),0))   Avg_Row_Length
from TABLENAME;


analyze table TABLENAME compute statistics;

select Num_Rows,              /*number of rows*/
       Blocks,                /*number of blocks used*/
       Num_Rows/Blocks        /*number of rows per block*/
  from USER_TABLES
 where Table_Name='TABLENAME';


select AVG(NVL(VSIZE(Column1),0))+
       AVG(NVL(VSIZE(Column2),0))   Avg_Row_Length
from TABLENAME;

select AVG(NVL(VSIZE(Column1),0))+
       AVG(NVL(VSIZE(Column2),0))   Avg_Row_Length_1
from TABLE1;

select AVG(NVL(VSIZE(Column1),0))   Avg_Row_Length_2
from TABLE2;

select
   COUNT(DISTINCT(column name))/    /* Num of records in table*/
   COUNT(*)  rows_per_key           /* Num of cluster key values*/
from tablename;

select
   AVG(NVL(VSIZE(cluster key column),0)) Avg_Key_Length
 from TABLE1;


create type ADDRESS_TY as object
(Street   VARCHAR2(50),
City      VARCHAR2(25),
State     CHAR(2),
Zip       NUMBER);

create table CUSTOMER
(Name     VARCHAR2(25),
Address   ADDRESS_TY);

insert into CUSTOMER values
(1,ADDRESS_TY('My Street', 'Some City', 'ST', 10001));



